/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package accesoDatos;

import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import logicaNegocio.Estudiante;
import oracle.jdbc.internal.OracleTypes;

/**
 *
 * 
 */
public class GestorEstudiante extends Conexion implements Gestor<Estudiante> {
    private static final String ingresarEstudiante="{call INSERTAR_ESTUDIANTE(?,?,?,?,?,?,?,?)}";
    private static final String consultarEstudiante="{?=call BUSCAR_ESTUDIANTE_ID(?)}";
    private static final String eliminarEstudiante="";
    private static final String modificarEstudiante="";
    private static final String listarEstudiante="";
    private static final String consultarEstudianteCarnet="{?=call BUSCAR_ESTUDIANTE_CARNET(?)}";

    @Override
    public void ingresarDato(Estudiante clase) {
        CallableStatement cs = null;
        try{
            if(!Conectar())
                throw null;
            cs = conexion.prepareCall(ingresarEstudiante);
            cs.setString(1, clase.getIdentificacion());
            cs.setString(2, clase.getCarnet());
            cs.setString(3, clase.getNombre());
            cs.setInt(4, clase.getEstado());
            cs.setString(5, clase.getCarrera());
            cs.setString(6, clase.getFacultad());
            cs.setString(7, clase.getEscuela());
            cs.setString(8, clase.getSede());
            boolean resultado = cs.execute();
            if(resultado){
                throw null;
            }
        }
        catch(SQLException ex){
            System.out.println(ex.getLocalizedMessage());
            return;
        }
        finally{
            try{
                if(cs != null)
                    cs.close();
                Desconectar();
            }
            catch(SQLException ex){
                System.out.println(ex.getLocalizedMessage());
                return;
            }
            catch(Exception ex){
                System.out.println(ex.getLocalizedMessage());
                return;
            }
        }
    }

    @Override
    public void eliminarDato(String identificacion) {
        CallableStatement ps = null;
        try{
            if(!this.Conectar())
                return;
            ps = conexion.prepareCall(eliminarEstudiante);
            ps.setString(1, identificacion);
            int resultado = ps.executeUpdate();
        }
        catch(SQLException ex){
            System.out.println(ex.getLocalizedMessage());
            return;
        }
        finally{
            try{
                if(ps == null)
                    ps.close();
                this.Desconectar();
            }
            catch(SQLException ex){
                System.out.println(ex.getLocalizedMessage());
                return;
            }
            catch(Exception ex){
                System.out.println(ex.getLocalizedMessage());
                return;
            }
        }
    }

    @Override
    public void modificarDato(Estudiante clase) {
        CallableStatement ps = null;
        try{
            if(this.Conectar())
                return;
            ps = this.conexion.prepareCall(modificarEstudiante);
            ps.setString(1, clase.getIdentificacion());
            ps.setString(2, clase.getCarnet());
            ps.setString(3, clase.getNombre());
            ps.setInt(4, clase.getEstado());
            ps.setString(5, clase.getCarrera());
            ps.setString(6, clase.getFacultad());
            ps.setString(7, clase.getEscuela());
            ps.setString(8, clase.getSede());
            int resultado = ps.executeUpdate();
        }
        catch(SQLException ex){
            System.out.println(ex.getLocalizedMessage());
            return;
        }
        finally{
            try{
            if(ps != null)
                ps.close();
            this.Desconectar();
            }
            catch(SQLException ex){
                System.out.println(ex.getLocalizedMessage());
                return;
            }
            catch(Exception ex){
                System.out.println(ex.getLocalizedMessage());
                return;
            }
        }
    }

    @Override
    public ArrayList<Estudiante> listarDato() {
        ResultSet rs = null;
        ArrayList<Estudiante> coleccion = new ArrayList<Estudiante>();
        Estudiante est = null;
        CallableStatement cs = null;
        try{
            if(!this.Conectar())
                return null;
            cs = this.conexion.prepareCall(listarEstudiante);
            cs.registerOutParameter(1, OracleTypes.CURSOR);
            cs.execute();
            rs = (ResultSet)cs.getObject(1);
            while(rs.next()){
                est = new Estudiante(
                        rs.getString(1),
                        rs.getString(2),
                        rs.getInt(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8)
                        );
                coleccion.add(est);
            }
        }
        catch(SQLException ex){
            System.out.println(ex.getLocalizedMessage());
            return null;
        }
        finally{
            try{
                if(rs != null)
                    rs.close();
                if(cs != null)
                    cs.close();
                this.Desconectar();
                if(coleccion == null || coleccion.isEmpty())
                    throw null;
            }
            catch(SQLException ex){
                System.out.println(ex.getLocalizedMessage());
                return null;
            }
            catch(Exception ex){
                System.out.println(ex.getLocalizedMessage());
                return null;
            }
        }
        return coleccion;
    }

    @Override
    public Estudiante consultarDato(String identificacion) {
        ResultSet rs = null;
        Estudiante estudiante = null;
        CallableStatement cs = null;
        try{
            if(!Conectar())
                throw null;
            cs = conexion.prepareCall(consultarEstudiante);
            cs.registerOutParameter(1,OracleTypes.CURSOR);
            cs.setString(2, identificacion);
            cs.execute();
            rs = (ResultSet)cs.getObject(1);
            while(rs.next()){
                estudiante = new Estudiante(
                        rs.getString(1),
                        rs.getString(2),
                        rs.getInt(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8)
                        );
            }
        }
        catch(SQLException ex){
            System.out.println(ex.getMessage());
            return null;
        }
        finally{
            try{
                if(rs != null)
                    rs.close();
                if(cs != null)
                    cs.close();
                Desconectar();
                if(estudiante == null)
                    throw null;
            }
            catch(SQLException ex){
                System.out.println(ex.getMessage());
                return null;
            }
            catch(Exception ex){
                System.out.println(ex.getMessage());
                return null;
            }
        }
        return estudiante;
    }
    
    public Estudiante consultarDatoPorCarnet(String carnet) {
        ResultSet rs = null;
        Estudiante estudiante = null;
        CallableStatement cs = null;
        try{
            if(!Conectar())
                throw null;
            cs = conexion.prepareCall(consultarEstudianteCarnet);
            cs.registerOutParameter(1,OracleTypes.CURSOR);
            cs.setString(2, carnet);
            cs.execute();
            rs = (ResultSet)cs.getObject(1);
            while(rs.next()){
                estudiante = new Estudiante(
                        rs.getString(1),
                        rs.getString(2),
                        rs.getInt(3),
                        rs.getString(4),
                        rs.getString(5),
                        rs.getString(6),
                        rs.getString(7),
                        rs.getString(8)
                        );
            }
        }
        catch(SQLException ex){
            System.out.println(ex.getMessage());
            return null;
        }
        finally{
            try{
                if(rs != null)
                    rs.close();
                if(cs != null)
                    cs.close();
                Desconectar();
                if(estudiante == null)
                    throw null;
            }
            catch(SQLException ex){
                System.out.println(ex.getMessage());
                return null;
            }
            catch(Exception ex){
                System.out.println(ex.getMessage());
                return null;
            }
        }
        return estudiante;
    }
    
    public Estudiante consultarIdPorRegistro(String id){
        Estudiante e = null;
        return e;
    }
    
    public Estudiante consultarCarnetPorRegistro(String id){
        Estudiante e = null;
        return e;
    }
}
